using DAL;
using Model;
using Newtonsoft.Json;
using SQLHelper;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
namespace 纺织贸易管理系统
{
    public  class Connect
    {
        public static string Environmen ;
        public static string SqlConnectString = string.Empty;
        public static SqlSugarClient dbcontext ;
        public static void SetDefault(string LabelName)
        {
            var sqliteconn = CreatSqlite();
            SQLiteCommand sQLiteCommand = new SQLiteCommand() { CommandText = $"update DefaultLabel set LabelName='{LabelName }'", Connection = sqliteconn };
            sQLiteCommand.ExecuteNonQuery();
        }
        public static List<DefaultLabel> GetDefault()
        {
            var sqliteconn = CreatSqlite();
            DataTable dt = new DataTable();
            SQLiteDataAdapter dp = new SQLiteDataAdapter("select * from DefaultLabel", sqliteconn);
            dp.Fill(dt);
            return JsonConvert.DeserializeObject<List<DefaultLabel>>(JsonConvert.SerializeObject(dt)); ;
        }
        public static SQLiteConnection CreatSqlite()
        {
            SQLiteConnection sqlconn = new SQLiteConnection();
            SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder
            {
                DataSource = System.Environment.CurrentDirectory + "\\Config.db"
            };
            sqlconn.ConnectionString = connstr.ToString();
            sqlconn.Open();
            return sqlconn;
        }
        public static DataTable GetFormName()
        {
            var db = new SQLHelper.SQLHelper(SqlConnectString);
            return db.Query("select distinct FormName from ColumnTable where UserID='10001' order by FormName");
        }
        public static DataTable GetGridName(string FormName)
        {
            var db = new SQLHelper.SQLHelper(SqlConnectString);
            return db.Query($"select distinct GridName from ColumnTable where UserID='10001' and FormName='{FormName}'");
        }
        public static List<ColumnTable> GetColumntable(string formname, string gridname, string userid)
        {
            var db = new SQLHelper.SQLHelper(SqlConnectString);
            return db.Query<ColumnTable>(x => x.FormName == formname && x.GridName == gridname && x.UserID == userid);
        }
        public static void  GetColumnSetting()
        {
            var sqliteconn = CreatSqlite();
            SQLiteDataAdapter dp = new SQLiteDataAdapter($"select * from ColumnSetting", sqliteconn);
            DataTable dt = new DataTable();
            dp.Fill(dt);
            ColumnSetting.OddColor = dt.Rows[0]["OddColor"].ToString();
            ColumnSetting.EvenColor = dt.Rows[0]["EvenColor"].ToString();
        }
       
        //设定奇数行颜色
        public static void SetOddColor(string color)
        {
            var sqliteconn = CreatSqlite();
            SQLiteCommand sQLiteCommand = new SQLiteCommand() { CommandText = $"Update ColumnSetting set OddColor='{color }'", Connection = sqliteconn };
            sQLiteCommand.ExecuteNonQuery();
        }
        //设定偶数行颜色
        public static void SetEvenColor(string color)
        {
            var sqliteconn = CreatSqlite();
            SQLiteCommand sQLiteCommand = new SQLiteCommand() { CommandText = $"Update ColumnSetting set EvenColor='{color }'", Connection = sqliteconn };
            sQLiteCommand.ExecuteNonQuery();
        }
        //
        public static void SaveColumnTable(List<ColumnTable > collist)
        {
            dbcontext.Insertable( collist).ExecuteCommand();
        }
        public static void DeleteColumnTable(string formname,string gridname,string userid)
        {           
            dbcontext.Deleteable <ColumnTable>().Where(x => x.FormName == formname &&x.GridName == gridname&&x.UserID==userid).ExecuteCommand();         
        }
        public   static void CreatConnectstring()
        {
                var sqliteconn = CreatSqlite();
                SQLiteDataAdapter dp = new SQLiteDataAdapter($"select * from linkinfo where Environment='{Environmen}'", sqliteconn);
                DataTable dt = new DataTable();
                dp.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    SqlConnectString= $"server={dt.Rows[0]["server"]},{dt.Rows[0]["port"]};uid={dt.Rows[0]["username"]};pwd={dt.Rows[0]["PWD"]};database={dt.Rows[0]["database"]}";
                }
            GetColumnSetting();
        }
        public static SQLHelper.SQLHelper CreatConnect()
        {            
           return  new SQLHelper.SQLHelper(SqlConnectString);
        }
        public static SqlSugarClient GetSugar()
        {
            return new SqlSugar.SqlSugarClient(new SqlSugar.ConnectionConfig()
            {
                ConnectionString = SqlConnectString,
                DbType = SqlSugar.DbType.SqlServer,
                IsAutoCloseConnection = true
            });
        }
        public static SQLHelper.SQLHelper CreatRemortConnect()
        {
            XmlDocument xml = new XmlDocument();
            xml.Load("LinkInfo.xml");
            var conn = new SQLHelper.ConnectInfo
            {
                Database = xml.SelectSingleNode("/Serverdatabase").InnerText,
                Port = xml.SelectSingleNode("/Serverport").InnerText,
                PWD = xml.SelectSingleNode("/Serverpwd").InnerText,
                UID = xml.SelectSingleNode("/Serveruid").InnerText,
                Server = xml.SelectSingleNode("/Serverserver").InnerText
            };
            var db = new SQLHelper.SQLHelper(conn);
            return db;
        }
        public static SqlSugar.SqlSugarClient SoftkcDBHelper()
        {
            return new SqlSugar.SqlSugarClient(new SqlSugar.ConnectionConfig()
            {
                ConnectionString = "server=softkc.cn,1433;uid=sa;pwd=Kc123456;database=finishdb",
                //ConnectionString = "server=192.168.0.109,1433;uid=sa;pwd=Kc123456;database=Fricedb;MultipleActiveResultSets=true;Max Pool Size = 512",
                DbType = SqlSugar.DbType.SqlServer,
                IsAutoCloseConnection = true,
            });
        }
    }
}
